﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using System.Linq;
using System.Data.Odbc;
using System.Collections;
namespace Exact_Extension
{
    public partial class InvoiceListForm : DevExpress.XtraEditors.XtraForm
    {
        ExactDataContext DS;
        IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
        ODBCIR odbcIR;
        public InvoiceListForm(ExactDataContext _DS)
        {
            InitializeComponent();
            DS = _DS;
            odbcIR = new ODBCIR();
            this.FromDateEdit.EditValue= DateTime.Now;
            this.ToDateEdit.EditValue = DateTime.Now;
            var queryItem = from SelectItem in DS.ISHCMC_V_Items
                            select SelectItem;
            this.iSHCMCVItemBindingSource.DataSource = queryItem;
        }
        public List<ISHCMC_DebtorRegister> GetListStudent()
        {
            OdbcConnection conn = new OdbcConnection("DSN=ISIT-INRE;Uid=admin;Pwd=r3dGr@ss;");
            conn.Open();


            List<ISHCMC_DebtorRegister> ListStudent = new List<ISHCMC_DebtorRegister>();

            string sqlComm = " Select   idInquiry,NameFirst+' ',NameFirst_ovr+' ',NameMiddle+' ' "
                + ",NameLast+' ',P1a_First+' ',P1a_Last+' ' , P1a_phone_cell+' ',P1a_email+' ',P1b_First+' ',P1b_Last+' '  "
                + " , P1b_phone_cell+ ' ',P1b_email+' ' , P1_phone_H +' ' , P1_Street +' ' , P1_City +' ' , P1_Country +' ',PWSPaymentMethod +' ' "
                + " , Billing_Name + ' ',Adm_Season + ' ',Campus + ' ',Grade_Current+' ',Adm_enr_status + ' '  ,   idInquiryArc + ' '   from INQUIRIES  ";




            OdbcCommand comm = new OdbcCommand();
            comm.Connection = conn;
            comm.CommandText = sqlComm;

            OdbcDataReader reader = comm.ExecuteReader();
            int i = 0;
            while (reader.Read())
            {

                ISHCMC_DebtorRegister item = new ISHCMC_DebtorRegister();

                item.StudentIDIS = reader.GetString(0);

                item.StudentFirstName = reader.GetString(1);
                item.StudentFirstName += reader.GetString(2);

                item.StudentLastName = reader.GetString(3);
                item.StudentLastName += reader.GetString(4);

                item.FatherName = reader.GetString(5);
                item.FatherName += reader.GetString(6);
                item.FatherCellPhone = reader.GetString(7);
                item.FatherEmail = reader.GetString(8);

                item.MotherName = reader.GetString(9);
                item.MotherName += reader.GetString(10);

                item.MotherCellPhone = reader.GetString(11);
                item.MotherEmail = reader.GetString(12);

                item.Homephone = reader.GetString(13);
                item.Homeaddress1 = reader.GetString(14);
                item.Homeaddress2 = reader.GetString(15);
                item.Homeaddress3 = reader.GetString(16);

                item.PaymentPlan = reader.GetString(17);
                if (item.PaymentPlan.Length > 0)
                {
                    item.PaymentPlan = item.PaymentPlan.Substring(0, 1);
                }
                else
                {
                    item.PaymentPlan = "A";
                }
                item.BillingName = reader.GetString(18);

                item.BillingAddress1 = reader.GetString(14);
                item.BillingAddress2 = reader.GetString(15);
                item.BillingAddress3 = reader.GetString(16);

                item.ContactName1 = item.FatherName;
                item.ContactName2 = item.MotherName;


                item.Contactaddress1 = reader.GetString(14);
                item.Contactaddress2 = reader.GetString(15);
                item.Contactaddress3 = reader.GetString(16);

                item.SchoolYear = reader.GetString(19);
                item.School = reader.GetString(20);
                if (item.School == "A")
                {
                    item.School = "AAVN";
                }
                else if (item.School == "I")
                {
                    item.School = "ISHCMC";
                }
                item.Grade = reader.GetString(21);
                if (item.Grade.StartsWith("E"))
                {
                    item.Grade = item.Grade.Substring(1, 2);
                }
                if (item.Grade.Length == 1)
                {
                    item.Grade = "0" + item.Grade;
                }
                //item.ActualEntryDate = Convert.ToDateTime(reader.GetString(22));
                // item.DOB = reader.GetDate(23);
                //item.Gender =  reader.GetString(24);
                item.StudentStatus = reader.GetString(22);
                item.StudentIDExact = reader.GetString(23);
                if (item.StudentIDExact.Length > 4)
                {
                    item.StudentIDIS = item.StudentIDExact.Trim();
                }

                ListStudent.Add(item);

            }
            reader.Close();

            conn.Close();
            return ListStudent;

        }
        private void btnLoad_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            DateTime fromDate = Convert.ToDateTime(Convert.ToDateTime(this.FromDateEdit.EditValue).ToString("dd/MM/yyyy"), culture);
            DateTime toDate = Convert.ToDateTime(Convert.ToDateTime(this.ToDateEdit.EditValue).ToString("dd/MM/yyyy"), culture);
            List<ISHCMC_F_BillingAmountResult> ListInvoice = new List<ISHCMC_F_BillingAmountResult>();
            Hashtable hashDataST = new Hashtable();

            foreach (ISHCMC_DebtorRegister itemDR in this.GetListStudent())
            {
                hashDataST[itemDR.StudentIDIS] = itemDR.StudentStatus;
            }
            var query = from InvList in DS.ISHCMC_F_BillingAmount(fromDate,toDate,this.cbxItemFee.EditValue.ToString())
                        select InvList;
            foreach (ISHCMC_F_BillingAmountResult item in query)
            {
                if (hashDataST.ContainsKey(item.StudentID))
                {
                    item.PortalStatus = hashDataST[item.StudentID].ToString();
                }
                if (item.DueDate.HasValue)
                {
                    item.DueDateStr = item.DueDate.Value.ToString("dd/MM/yyyy");
                }
                if (item.DateOfSOExact.HasValue)
                {
                    item.DateOfSOExactStr = item.DateOfSOExact.Value.ToString("dd/MM/yyyy");
                }
                if (item.ActualWithdrawDate.HasValue)
                {
                    item.ActualWithdrawDateStr = item.ActualWithdrawDate.Value.ToString("dd/MM/yyyy");
                }
                if (item.TransDate.HasValue)
                {
                    item.TransDateStr = item.TransDate.Value.ToString("dd/MM/yyyy");
                }
                ListInvoice.Add(item);
            }
            this.iSHCMCFBillingAmountResultBindingSource.DataSource = ListInvoice;
        }

        private void btnExport_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
        {
            this.pivotGridControl1.ShowRibbonPrintPreview();
        }
    }
}